﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data;
using System.Configuration;
using System.Text;
using System.IO;
using System.Collections;

/// <summary>
/// Summary description for SQLInteract
/// </summary>
public class SQLInteract
{
    Logger objLog = new Logger();
    SqlDataReader datareader;

    private SqlCommand ReturnCommand()
    {
        SqlConnection connection = null;
        connection = OpenNewConnection();
        SqlCommand command = connection.CreateCommand();
        try
        {
            command.CommandType = CommandType.Text;
            command.Connection = connection;            
        }
        catch (Exception ex)
        {
            objLog.WriteClassErr(ex.Message.ToString(), DateTime.Now.ToString(), "ReturnCommand", "SQLInteract");
        }
        return command;
    }

    public SqlDataReader ReturnDataReader(string QueryString)
    {
        SqlCommand command = ReturnCommand();
        command.CommandText = QueryString;
        try
        {
            datareader = command.ExecuteReader();
        }
        catch (Exception ex)
        {
            objLog.WriteClassErr(ex.Message.ToString(), DateTime.Now.ToString(), "ReturnDataReader", "SQLInteract");
        }
        return datareader;
    }

    public SqlConnection OpenNewConnection()
    {
        SqlConnection sConn = new SqlConnection(ConfigurationManager.ConnectionStrings["PortalConnectionString"].ConnectionString);
        try
        {
        sConn.Open();
        }
        catch(Exception ex)
        {
            objLog.WriteClassErr(ex.Message.ToString(), DateTime.Now.ToString(), "OpenNewConnection", "SQLInteract");
        }
        return sConn;
    }

    public DataSet ReturnDataAdapter(string QueryString)
    {
        SqlDataAdapter dataadapter = null;
        DataSet dtSet = null;
        SqlCommand command = null;
        try
        {
            using (SqlConnection sConn = new SqlConnection(ConfigurationManager.ConnectionStrings["PortalConnectionString"].ConnectionString))
            {
                if (sConn.State == ConnectionState.Closed)
                {
                    sConn.Open();
                }
                command = new SqlCommand(QueryString, sConn);
                dataadapter = new SqlDataAdapter(command);
                dtSet = new DataSet();
                dataadapter.Fill(dtSet);
            }
        }
        catch (Exception ex)
        {
            objLog.WriteClassErr(ex.Message.ToString(), DateTime.Now.ToString(), "ReturnDataAdapter", "SQLInteract");
        }
        finally
        {
            dataadapter.Dispose();
            command.Dispose();
        }
        return dtSet;
    }

    public int sqlExecuteNonQuery(string QueryString)
    {
        SqlCommand command = null;
        int affectedCount = 0;
        try
        {
            using (SqlConnection sConn = new SqlConnection(ConfigurationManager.ConnectionStrings["PortalConnectionString"].ConnectionString))
            {
                if (sConn.State == ConnectionState.Closed)
                {
                    sConn.Open();
                }
                command = new SqlCommand(QueryString, sConn);
                affectedCount = command.ExecuteNonQuery();
            }
        }
        catch (Exception ex)
        {
            objLog.WriteClassErr(ex.Message.ToString(), DateTime.Now.ToString(), "sqlExecuteScalar", "SQLInteract");
        }
        finally
        {
            command.Dispose();
        }
        return affectedCount;
    }

    public string sqlExecuteScalar(string QueryString)
    {
        SqlCommand command = null;
        string columnData = "";
        try
        {
            using (SqlConnection sConn = new SqlConnection(ConfigurationManager.ConnectionStrings["PortalConnectionString"].ConnectionString))
            {
                if (sConn.State == ConnectionState.Closed)
                {
                    sConn.Open();
                }
                command = new SqlCommand(QueryString, sConn);
                columnData = command.ExecuteScalar().ToString();
            }
        }
        catch (Exception ex)
        {
            objLog.WriteClassErr(ex.Message.ToString(), DateTime.Now.ToString(), "sqlExecuteScalar", "SQLInteract");
        }
        finally
        {
            command.Dispose();
        }
        return columnData;
    }
}